# coding=utf-8
import sqlite3
from module import GlobalData
from module.common_utils import get_today_time
import json
class DAO :
    def __init__(self,table=None):
        dbname = GlobalData.DATA_PATH+'/guest.db'
        self.tablename=table
        self.database_name = dbname
        self.init_database()

    def init_database(self):
        self.conn = sqlite3.connect(self.database_name)
        self.conn.text_factory = str
        self.conn.row_factory = sqlite3.Row
        '''
        匿名话题
        '''
        self.conn.execute('create table if not exists guest_topic('
                          'id varchar(128) primary key,'
                          'subject varchar(255),' 
                          'color varchar(10),'
                          'content text,'
                          'createtime number,'
                          'updatetime number,'
                          'comment int default 0,'
                          'agree int default 0,'
                          'top int default 0,'
                          'oppose int default 0'
                          ')')
        '''
        匿名评论
        '''
        self.conn.execute('create table if not exists guest_comment('
                          'id varchar(128) primary key,'
                          'topic varchar(128),'
                          'comment varchar(128),'
                          'content text,'
                          'createtime number,'
                          'updatetime number,'
                          'agree int default 0,'
                          'oppose int default 0'
                          ')')
        self.conn.execute('create table if not exists history_event('
                          'id varchar(128) primary key,'
                          'month varchar(10),'
                          'content text,'
                          'updatetime number'
                          ')')
        self.isclose = False
        if not self.checkColumnExist('guest_topic', 'color'):
            self.conn.execute('ALTER TABLE guest_topic ADD color varchar(10)')

    def reopen(self):
        self.init_database()
    def checkColumnExist(self, tablename, columnname):
        result = False
        try:
            cur = self.conn.cursor()
            cur.execute('select * from ' + tablename + ' limit 0')
            col_name_list = [tuple[0] for tuple in cur.description]
            result = (columnname in col_name_list)
            cur.close()
        except Exception as ex:
            print(ex)
        return result
    def close(self):
        self.conn.close()
        self.isclose = True
    def commit(self):
        self.conn.commit()
    def execute(self,sql,args):
        if self.isclose:
            self.reopen()
        self.conn.execute(sql,args)
        self.conn.commit()
    def query(self,sql,args):
        if self.isclose:
            self.reopen()
        cur = self.conn.cursor()
        cur.execute(sql, args)
        rows = cur.fetchall()
        cur.close()
        return self.rows_to_dicts(rows)
    def row_to_dict(self, row):
        if row is None:
            return None
        rs = {}
        keys = row.keys()
        for key in keys:
            tmp = row[key]
            if type(tmp) == str and (tmp.startswith('{') or tmp.startswith('[')):
                tmp = json.loads(tmp)
            rs[key] = tmp
        return rs
    def rows_to_dicts(self, rows):
        dict = []
        for row in rows:
            rs = {}
            keys = row.keys()
            for key in keys:
                tmp = row[key]
                if type(tmp)==str and (tmp.startswith('{') or tmp.startswith('[')):
                    try:
                        tmp = json.loads(tmp)
                    except:
                        pass
                rs[key] = tmp
            dict.append(rs)
        return dict
    def insert(self,**kwargs):
        tablename = self.tablename
        if 'table' in kwargs:
            tablename = kwargs['table']
            kwargs.pop('table')
        sql = 'insert into '+tablename
        fields = []
        values = []
        params = []
        for k,v in kwargs.items():
            fields.append(k)
            params.append("?")
            if type(v)==dict or type(v)==list:
                values.append(json.dumps(v,ensure_ascii=False))
            else:
                values.append(v)
        sql+='('+','.join(fields)+') values('+','.join(params)+')'
        if self.isclose:
            self.reopen()
        self.conn.execute(sql,values)
        self.commit()
    def update(self,table=None,rowkey='',increment=None,where=None,args=None,**kwargs):
        tablename = self.tablename
        if table!=None:
            tablename = table
        sql = 'update '+tablename+' set '
        values = []
        for k, v in kwargs.items():
            if v=='increment':
                sql+=k+'='+k+'+1,'
            elif v=='decrement':
                sql+=k+'='+k+'-1,'
            else:
                sql+=k+'=?,'
                if type(v)==dict or type(v)==list:
                    values.append(json.dumps(v,ensure_ascii=False))
                else:
                    values.append(v)
        if increment is not None:
            sql+=' '+increment+'='+increment+'+1'
        else:
            sql = sql[:-1]
        if where is not None:
            sql+=' where '+where
            values.extend(args)
        else:
            sql+=' where id=?'
            values.append(rowkey)
        if self.isclose:
            self.reopen()

        self.conn.execute(sql, values)
        self.commit()
    def remove(self,**kwargs):
        tablename = self.tablename
        if 'table' in kwargs:
            tablename = kwargs['table']
            kwargs.pop('table')
        sql = 'delete from '+tablename
        values = []
        first = True
        for k, v in kwargs.items():
            if first:
                sql+=' where '+k+'=?'
            else:
                sql+=' and '+k+'=?'
            first = False
            values.append(v)
        if self.isclose:
            self.reopen()
        self.conn.execute(sql,values)
        self.conn.commit()
    def load_today(self,fields=None,timefield='id',like=None,orderby=None,desc=False,**kwargs):
        start, end = get_today_time()
        items = self.load(fields=fields,orderby=orderby,
                          desc=desc,like=like,
                          timefield=timefield,
                          starttime=start,endtime=end,**kwargs)
        return items
    def loadone(self,table=None,like=None,orderby=None,desc=False,**kwargs):
        tablename = self.tablename
        if table != None:
            tablename = table
        sql = 'select * from '+tablename
        values = []
        first = True
        for k, v in kwargs.items():
            if first:
                sql += ' where ' + k + '=?'
            else:
                sql += ' and ' + k + '=?'
            first = False
            values.append(v)
        if orderby is not None:
            if type(orderby)==list:
                sql+=' order by '
                for o in orderby:
                    sql+=o+','
                sql = sql[:-1]
            else:
                sql +=' order by '+orderby
            if desc:
                sql += ' desc'
        sql+=' limit 1'
        if self.isclose:
            self.reopen()
        cur = self.conn.cursor()
        cur.execute(sql, values)
        row=cur.fetchone()
        cur.close()
        return self.row_to_dict(row)
    def load(self,fields=None,orderby=None,desc=False,like=None,start=None,limit=None,**kwargs):
        tablename = self.tablename
        if 'table' in kwargs:
            tablename = kwargs['table']
            kwargs.pop('table')
        sql = 'select '
        if fields is None or len(fields)==0:
            sql+='*'
        else:
            sql+=','.join(fields)
        sql+=' from '+tablename+' where (1=1)'
        values = []
        if like!=None:
            for field,value in like.items():
                sql+=' and '+field+' like ?'
                values.append('%'+value+'%')

        time_field = ''
        if 'timefield' in kwargs:
            time_field = kwargs['timefield']
            kwargs.pop('timefield')
        for k, v in kwargs.items():
            if v is None or len(v)==0:
                continue
            if 'starttime' == k:
                sql += ' and ' + time_field + '>=?'
            elif 'endtime' == k:
                sql += ' and ' + time_field + '<=?'
            else:
                sql += ' and ' + k + '=?'
            values.append(v)
        if orderby is not None:
            if type(orderby)==list:
                sql+=' order by '
                for o in orderby:
                    sql+=o+','
                sql = sql[:-1]
            else:
                sql +=' order by '+orderby
            if desc:
                sql += ' desc'
        if start!=None:
            sql+=' limit '+str(start)
        if limit!=None:
            if 'limit' in sql:
                sql+=','+str(limit)
            else:
                sql+=' limit '+str(limit)
        if self.isclose:
            self.reopen()
        print(sql)
        cur = self.conn.cursor()
        cur.execute(sql,values)
        rows = cur.fetchall()
        cur.close()
        return self.rows_to_dicts(rows)
    def query_by_sql(self,sql,args):
        cur= self.conn.cursor()
        cur.execute(sql,args)
        rows = cur.fetchall()
        cur.close()
        return self.rows_to_dicts(rows)

    def count(self,table=None,like=None,**kwargs):
        tablename = self.tablename
        if table!=None and len(table)>0:
            tablename =table
        sql = 'select count(*) as total'
        sql+=' from '+tablename+' where (1=1)'
        values = []
        if like!=None:
            for field,value in like.items():
                sql+=' and '+field+' like ?'
                values.append('%'+value+'%')

        time_field = ''
        if 'timefield' in kwargs:
            time_field = kwargs['timefield']
            kwargs.pop('timefield')
        for k, v in kwargs.items():
            if v is None :
                continue
            if 'starttime' == k:
                sql += ' and ' + time_field + '>=?'
            elif 'endtime' == k:
                sql += ' and ' + time_field + '<=?'
            else:
                sql += ' and ' + k + '=?'
            values.append(v)
        if self.isclose:
            self.reopen()
        cur = self.conn.cursor()
        cur.execute(sql,values)
        rows = cur.fetchone()
        count = rows['total']
        cur.close()
        return count

    def sum(self,table=None,field='id',like=None,**kwargs):
        tablename = self.tablename
        if table!=None and len(table)>0:
            tablename =table
        sql = 'select sum('+field+') as total'
        sql+=' from '+tablename+' where (1=1)'
        values = []
        if like!=None:
            for field,value in like.items():
                sql+=' and '+field+' like ?'
                values.append('%'+value+'%')

        time_field = ''
        if 'timefield' in kwargs:
            time_field = kwargs['timefield']
            kwargs.pop('timefield')
        for k, v in kwargs.items():
            if v is None :
                continue
            if 'starttime' == k:
                sql += ' and ' + time_field + '>=?'
            elif 'endtime' == k:
                sql += ' and ' + time_field + '<=?'
            else:
                sql += ' and ' + k + '=?'
            values.append(v)
        if self.isclose:
            self.reopen()
        cur = self.conn.cursor()
        cur.execute(sql,values)
        rows = cur.fetchone()
        count = rows['total']
        cur.close()
        return count

